import numpy as np
import pandas as pd
import os
import time
import glob
import re
import warnings
from program.pack.append_df_to_excel import append_df_to_excel
from program.pack.wsa_to_buwei import wsa_buwei, wsa_yangpin, wsa_beizhu
from pandas import to_datetime
from functools import reduce

warnings.filterwarnings("ignore")

# 列显示不全，进行设置
pd.set_option('display.max_columns', 500)
pd.set_option('display.unicode.ambiguous_as_wide', True)
pd.set_option('display.unicode.east_asian_width', True)
pd.set_option('display.width', 180)  # 设置打印宽度(**重要**)


def func_image(path_inputs=[r'I:\Data\0513新疆机场3'], endswith_false='Data.xlsx', endswith='.xlsx'):
    dfs_d = pd.DataFrame()
    dfs_o = pd.DataFrame()
    k = 0
    l = 0
    for path_input in path_inputs:  # 支持多文件夹遍历
        # os.walk(file_path) 深度遍历file_path下的所有子文件夹及文件
        for root_dirs, sub_dirs, files in os.walk(path_input):
            # 读取detectImage地址,图片组数，以key_id为键
            if root_dirs.endswith('detectImage') and ('kongsao' not in root_dirs.lower()):
                try:
                    df_dd = pd.DataFrame({'detectImage文件名': sub_dirs})
                    df_dd['detectImage地址'] = root_dirs
                    # key_id = root_dirs.lower().split('\\data\\')[0]
                    if root_dirs.lower().startswith('i:'):
                        # i(移动硬盘)上存在data文件夹，故为避免切片错误，所以进行判断分支
                        key_id = root_dirs.lower().split('\\data_x')[0]
                    elif root_dirs.lower().startswith('z:') or root_dirs.lower().startswith('w:'):
                        key_id = root_dirs.lower().split('\\data')[0]
                    else:
                        key_id = root_dirs.lower().split('\\data')[0]
                    key_id = key_id.split('\\detectimage')[0]
                    df_dd['key_d'] = key_id + '_' + df_dd['detectImage文件名'].str.split('_', expand=True)[0]
                    list_fb = os.listdir(os.path.join(root_dirs, sub_dirs[0]))
                    list_fb = [i for i in list_fb if i.endswith('.bmp')]
                    df_dd['detectImage图片数'] = int(len(list_fb))
                    dfs_d = pd.concat([dfs_d, df_dd])
                    k += 1
                    print(root_dirs, '第', k, '个detectimage读取成功')
                except Exception as e:
                    print(root_dirs, e)
                    # 读取detectImage地址,图片组数，以key_id为键
            if root_dirs.endswith('originImage') and ('kongsao' not in root_dirs.lower()):
                try:
                    df_oo = pd.DataFrame({'originImage文件名': sub_dirs})
                    df_oo['originImage地址'] = root_dirs
                    # key_id = root_dirs.lower().split('\\data\\')[0]
                    if root_dirs.lower().startswith('i:'):
                        # i(移动硬盘)上存在data文件夹，故为避免切片错误，所以进行判断分支
                        key_id = root_dirs.lower().split('\\data_x')[0]
                    elif root_dirs.lower().startswith('z:') or root_dirs.lower().startswith('y:'):
                        key_id = root_dirs.lower().split('\\data')[0]
                    key_id = key_id.split('\\originimage')[0]
                    df_oo['key_o'] = key_id + '_' + df_oo['originImage文件名'].str.split('_', expand=True)[0]
                    list_fb = os.listdir(os.path.join(root_dirs, sub_dirs[0]))
                    list_fb = [i for i in list_fb if i.endswith('.bmp')]
                    df_oo['originImage图片数'] = int(len(list_fb))
                    dfs_o = pd.concat([dfs_o, df_oo])
                    l += 1
                    print(root_dirs, '第', l, '个originImage读取成功')
                except Exception as e:
                    print(root_dirs, e)
    # dfs_image = pd.merge(dfs_0, dfs_1, how='outer', on=['key_id'])
    # k_columns = ['key_id', 'key_d', 'detectImage地址', 'originImage地址', 'detectImage文件名', 'originImage文件名',
    #              'detectImage图片组数', 'originImage图片组数']
    # dfs_image = dfs_image[k_columns]
    return dfs_o, dfs_d


# 对DataFrame 进行各项填充
def func_0(data_df):
    # 默认取前8列
    # data_df.values[3:4, 8:9] = '误报说明'
    if data_df.shape[1] == 8:
        data_df['误报说明'] = ''
    elif data_df.shape[1] == 9:
        pass
    else:
        print(data_df.shape[1], '行数异常')

    data_2 = data_df.iloc[4:, 0:10]
    list_columns = ['序号o', '样品', '身体部位', '部位编号', '是否检出', '是否可见', '是否误报', '备注', '误报说明', '人工可见']
    data_2.columns = list_columns
    # if (data_2.values[2][7] == '备注') and (data_2.values[3][7] != '' and len(str(data_2.values[3][7])) > 10):
    #     data_2['序号d'] = data_2['序号d'].astype(str)
    # else:
    #     data_2['序号d'] = data_2['序号o'].astype(str)
    data_2['序号d'] = data_2['序号o'].astype(str)
    # 删除行中小于等于3个空值的行，删除没有序号的行
    data_2 = data_2.dropna(axis=0, thresh=3)
    data_2 = data_2.drop(data_2[data_2['序号o'].isnull()].index)
    # data_2['序号'] = data_2['序号'].astype(int)

    # 对样品进行数据填充
    data_3 = data_df.iloc[0:3, 1:9]
    data_2['样品'].fillna(method='ffill', inplace=True)
    data_2['样品'] = data_2['样品'].str.upper()
    data_2['备注'] = data_2['备注'].str.upper()
    # 其他标准信息填充
    data_2['姓名'] = data_3.values[0][0]
    data_2['性别'] = data_3.values[0][1]
    data_2['年龄'] = data_3.values[0][2]
    data_2['身高cm'] = data_3.values[0][3]
    data_2['体重kg'] = data_3.values[0][4]
    data_2['体重kg'] = data_2['体重kg'].astype(str).str.extract(r'(\d+)', expand=False)
    data_2[['年龄', '身高cm', '体重kg']] = data_2[['年龄', '身高cm', '体重kg']].fillna(-999)
    # data_2[['年龄', '身高cm', '体重kg']] = data_2[['年龄', '身高cm', '体重kg']].astype(int)
    data_2['BMI'] = data_3.values[0][5]
    # 对bmi缺失部分进行计算填入
    if np.isnan(data_3.values[0][5]):
        data_2['体重kg'] = data_2['体重kg'].astype(int)
        data_2['身高cm'] = data_2['身高cm'].astype(int)
        data_2['BMI'] = 100 * 100 * data_2['体重kg'] / (data_2['身高cm'] ** 2)
    data_2['BMI'] = data_2['BMI'].round(1)
    data_2['BMI体质'] = data_2['BMI'].apply(func_bmi)
    data_2['采集方案'] = data_3.values[0][6]
    data_2['配饰'] = data_3.values[0][7]
    data_2['配饰'] = data_2['配饰'].replace('选填', '')
    data_2['衣物季节'] = data_3.values[2][0]
    data_2['姿势'] = data_3.values[2][1]
    data_2['工作人员'] = data_3.values[2][2]
    data_2['机器编号'] = data_3.values[2][3]
    data_2['模型版本'] = data_3.values[2][4]
    data_2['上衣'] = data_3.values[2][5]
    data_2['下衣'] = data_3.values[2][6]
    data_2['鞋子'] = data_3.values[2][7]
    return data_2


# 做统计计算
def func_bmi(bmi):
    if 12 < bmi < 18.5:
        return "偏瘦:<18.5"
    elif 24 > bmi >= 18.5:
        return "正常:18.5-23.9"
    elif 27 > bmi >= 24:
        return "偏胖:24-26.9"
    elif 30 > bmi >= 27:
        return "肥胖:27-29.9"
    elif 30 >= bmi >= 27:
        return "重度肥胖:≧30"
    else:
        return '未知'


def func_0x(path_inputs=[r'I:\Data\0513新疆机场3'], endswith_false='data.xlsx', endswith='.xlsx', inwith_false='16'):
    k = 0
    dfs_1 = pd.DataFrame()
    for path_input in path_inputs:  # 支持多文件夹遍历
        # os.walk(file_path) 深度遍历file_path下的所有子文件夹及文件
        for root_dirs, sub_dirs, files in os.walk(path_input):
            # 读取xlsx,以key_id为键
            for file in files:
                # 进行条件筛选   以非~$'开头(解决读取文档已打开问题)；选择以file_name_0结尾的文档
                if (file.startswith('~$') is False) and (file.endswith(endswith)) and (inwith_false not in file) and \
                        (file.lower().endswith(endswith_false) is False):
                    # 构造文件的绝对路径 = 文件夹路径 + 文件名
                    try:
                        file_path = os.path.join(root_dirs, file)
                        data_1 = pd.read_excel(file_path)
                        data_1 = func_0(data_1)
                        data_1 = data_1.drop(data_1[data_1['序号o'].isnull()].index)
                        data_1['文件名'] = file
                        data_1['文件夹地址'] = root_dirs
                        if root_dirs.lower().startswith('i:'):
                            # i(移动硬盘)上存在data文件夹，故为避免切片错误，所以进行判断分支
                            key_id = root_dirs.lower().split('\\data_x')[0]
                        elif root_dirs.lower().startswith('z:') or root_dirs.lower().startswith('w:'):
                            key_id = root_dirs.lower().split('\\data')[0]
                        else:
                            key_id = root_dirs.lower().split('\\data')[0]
                        key_id = key_id.split('\\detectimage|\\originimage')[0]
                        data_1['key_o'] = key_id + '_' + data_1['序号o'].astype(str).str.split('_').str[0]
                        data_1['key_d'] = key_id + '_' + data_1['序号d'].astype(str).str.split('_').str[0]
                        data_1[['key_o', 'key_d']] = data_1[['key_o', 'key_d']].fillna(-999)
                        dfs_1 = pd.concat([dfs_1, data_1])
                        k += 1
                        print(file, '第', k, '个表读取成功')
                    except Exception as e:
                        print(file, e)
    dfs_1.sort_values(by=['文件名', '序号o', '是否检出'], ascending=True, inplace=True)
    dfs_1 = dfs_1.drop_duplicates(['key_o'], 'last')
    return dfs_1


def func_error(data):
    data_error = data[data['BMI'].isnull() | data['样品'].isnull() | data['部位编号'].isnull() | data['是否检出'].isnull() |
                      data['是否可见'].isnull() | ((data['是否误报'] == 1) & (data['备注'].isnull())) |
                      ((data['是否误报'] == 0) & (data['备注'].notnull()) & (data['备注'] != ''))]
    data_error = data_error[
        ['序号o', '姓名', '性别', '年龄', '身高cm', '体重kg', 'BMI', 'BMI体质', '衣物季节', '上衣', '下衣', '鞋子', '配饰', '姿势',
         '工作人员', '机器编号', '模型版本', '采集方案', '样品大类', '样品名称', '样品', '身体大类', '身体部位', '部位编号', '是否检出',
         '是否可见', '是否误报', '人工可见', '备注', '备注_1', '备注_2', '备注_3', '备注_4', '误报说明', '文件名', '文件夹地址']]

    return data_error


def data_xlsx(endswith=".xlsx", endswith_false="data.xlsx", sheet_name='1.111原始数据', inwith_false="16"):
    # 合并所有符合条件的文件，并修整表头消息
    dfs_0 = func_0x(path_inputs, endswith=endswith, endswith_false=endswith_false, inwith_false=inwith_false)
    # 备注（误报）格式修改（多个误报分离）
    dfs_0 = wsa_beizhu(dfs_0)
    # 身体部位填充（部位编号-身体部位-身体大类）
    dfs_0 = wsa_buwei(dfs_0)
    # 样品数据填充（样品-样品名称-样品大类）
    # dfs_0.loc[dfs_0['样品'] == 'WOTCH5', '样品'] = 'WATCH5'
    # dfs_0.loc[dfs_0['样品'] == 'W0TCH5', '样品'] = 'WATCH5'

    # dfs_0.loc[dfs_0['备注'].notnull() & (dfs_0['备注'] != ''), '是否误报'] = 1
    dfs_0 = wsa_yangpin(dfs_0)
    # 某些人群处理
    #
    # dfs_0.loc[dfs_0['姓名'] == '农朱茵', '体重kg'] = 44
    # dfs_0.loc[dfs_0['姓名'] == '农朱茵', 'BMI'] = 18.8
    # dfs_0.loc[dfs_0['姓名'] == '农朱茵', 'BMI体质'] = "正常:18.5-23.9"

    k_columns = ['序号o', '序号d', '姓名', '性别', '年龄', '身高cm', '体重kg', 'BMI', 'BMI体质', '衣物季节', '上衣', '下衣', '鞋子', '配饰', '姿势',
                 '工作人员', '机器编号', '模型版本', '采集方案', '样品大类', '样品名称', '样品', '身体大类', '身体部位',
                 '部位编号', '是否检出', '是否可见', '是否误报', '人工可见', '备注', '备注_1', '备注_2', '备注_3', '备注_4', '误报说明',
                 '文件名', '文件夹地址', 'key_o', 'key_d', ]
    dfs_0 = dfs_0[k_columns]
    # dfs_0.to_excel('C:\Users\wangshuan\Desktop\\查看文件问题.xlsx')
    print('------------返回data文件程序已结束-----------')
    return dfs_0


# --------------txt --------------
def func_txt(data_txt):
    data_txt = data_txt.replace(' ' * 2, ' ', regex=True)
    data_txt['Image标记'] = data_txt['文件'].astype(str).str.split('\\').str[0]
    data_txt['Image文件'] = data_txt['文件'].astype(str).str.split('\\').str[1]
    data_txt['图片'] = data_txt['文件'].astype(str).str.split('\\').str[2]
    data_txt['图片'] = data_txt['图片'].astype(str)
    data_txt['图片'] = data_txt['图片'].str.split('.bmp').str[0]
    data_txt['类型'] = data_txt['文件'].astype(str).str.split(' ').str[1]
    data_txt['坐标'] = data_txt['文件'].astype(str).str.split(' ').str[2:6]
    data_txt = data_txt.apply(lambda x: x.replace('\n', '', regex=True).replace('\r', '', regex=True))  # 去除换行符，一般方法无法去掉
    return data_txt


def func_txt_x(path_inputs=[r'Z:\minhang_tai40_1\train\20210513xinjiang_train\0521']):
    k = 0
    dfs = pd.DataFrame()
    for path_input in path_inputs:  # 支持多文件夹遍历
        # os.walk(file_path) 深度遍历file_path下的所有子文件夹及文件
        for root_dir, sub_dir, files in os.walk(path_input):
            for file in files:
                # 进行条件筛选   以非~$'开头(解决读取文档已打开问题)；选择以file_name_0结尾的文档
                if (file.startswith('~$') is False) and file.endswith('label_lc_ok.txt'):
                    # 构造文件的绝对路径 = 文件夹路径 + 文件
                    file_name = os.path.join(root_dir, file)  # txt 文件地址
                    file = open(file_name, "r")  # 读取txt
                    list_txt = file.read().splitlines()  # 每一行数据写入到list中
                    data_t = pd.DataFrame({'文件': list_txt})  # 转化为dataframe
                    # data_t.sort_values(by=['文件名', '序号o', '是否检出'], ascending=True, inplace=True)# 排序
                    data_t = func_txt(data_t)
                    data_t = data_t[data_t['类型'] == 'object']
                    data_t['label文件地址'] = root_dir

                    key_id = root_dir.lower().split('\\data')[0]
                    key_id = key_id.split('\\originimage')[0]

                    if data_t.values[2][1] == 'originImage':
                        data_t['originImage文件名'] = data_t['Image文件']
                        data_t['detectImage文件名'] = ''
                        data_t['key_o'] = key_id + '_' + data_t['originImage文件名'].astype(str).str.split('_').str[0]
                        data_t['key_d'] = ''
                    elif data_t.values[2][1] == 'detectImage':
                        data_t['originImage文件名'] = ''
                        data_t['detectImage文件名'] = data_t['Image文件']
                        data_t['key_o'] = ''
                        data_t['key_d'] = key_id + '_' + data_t['detectImage文件名'].astype(str).str.split('_').str[0]
                    else:
                        print('Image傻傻分不清')
                    del data_t['文件']
                    del data_t['Image文件']
                    dfs = pd.concat([dfs, data_t])
                    k += 1
                    print(root_dir, '第', k, '个txt文件读取成功')
    return dfs


def dic_pd(data, columns='图片', image='Image文件'):
    data_dict = data.groupby(image)[columns].apply(list).to_dict()
    data_dict = pd.DataFrame([data_dict]).T
    data_dict = data_dict.reset_index()
    data_dict.columns = [image, columns]
    return data_dict


def fanzhuan(data):
    data_d1 = dic_pd(data=data, columns='图片', image='key_d')
    data_d2 = dic_pd(data=data, columns='坐标', image='key_d')
    dfs = [data_d1, data_d2]
    data_d = reduce(lambda left, right: pd.merge(left, right, on='key_d', how='left'), dfs)
    data_d['正'] = data_d['图片'].astype(str).str.count('front')
    data_d['反'] = data_d['图片'].astype(str).str.count('back')
    data_d['max'] = data_d[['正', '反']].max(axis=1)
    data_d[['sum']] = data_d[['正', '反']].sum(axis=1)

    data_o1 = dic_pd(data=data, columns='图片', image='key_o')
    data_o2 = dic_pd(data=data, columns='坐标', image='key_o')
    dfs = [data_o1, data_o2]
    data_o = reduce(lambda left, right: pd.merge(left, right, on='key_o', how='left'), dfs)
    data_o['正'] = data_o['图片'].astype(str).str.count('front')
    data_o['反'] = data_o['图片'].astype(str).str.count('back')
    data_o['max'] = data_o[['正', '反']].max(axis=1)
    data_o[['sum']] = data_o[['正', '反']].sum(axis=1)

    data_o = data_o.drop(0, axis=0, inplace=False)
    data_o.columns = ['key_o', '图片_o', '坐标_o', '正_o', '反_o', 'max_o', 'sum_o']
    data_d.columns = ['key_d', '图片_d', '坐标_d', '正_d', '反_d', 'max_d', 'sum_d']
    data_d = data_d.drop(0, axis=0, inplace=False)
    return data_d, data_o
    print('---------txt程序已结束---------')


# ---------txt程序已结束---------

# 尝试data——txt合并
def data_txt(data_xlsx):
    # detectImage文件名，文件地址，key，图片数；originImage....
    dfs_o, dfs_d = func_image(path_inputs, endswith_false='Data.xlsx', endswith='.xlsx')
    try:
        # txt文件读取写入, [key_d，图片，坐标，正，反，max], [key_o，图片，坐标，正，反，max]
        data_txt = func_txt_x(path_inputs=path_inputs)
        data_txt_d, data_txt_o = fanzhuan(data_txt)

        # 疯狂连接，啦啦啦啦啦
        data_xlsx = pd.merge(data_xlsx, dfs_o, how='left', on=['key_o'])
        data_xlsx = pd.merge(data_xlsx, dfs_d, how='left', on=['key_d'])

        data_xlsx = pd.merge(data_xlsx, data_txt_o, how='left', on=['key_o'])
        data_xlsx = pd.merge(data_xlsx, data_txt_d, how='left', on=['key_d'])
        list_dizhi = ['文件夹地址', 'key_o', 'key_d', 'originImage地址', 'detectImage地址']
        for i in range(len(list_dizhi)):
            data_xlsx[list_dizhi[i]] = data_xlsx[list_dizhi[i]].str.replace('W:', 'w:')
            data_xlsx[list_dizhi[i]] = data_xlsx[list_dizhi[i]].str.replace('w:', r'\\\\172.18.5.225\\data2')
            data_xlsx[list_dizhi[i]] = data_xlsx[list_dizhi[i]].str.replace('Z:', 'z:')
            data_xlsx[list_dizhi[i]] = data_xlsx[list_dizhi[i]].str.replace('z:', r'\\\\172.18.5.222\\data2')
        data_xlsx = data_xlsx.drop(data_xlsx[data_xlsx['是否可见'].isnull()].index)

        return data_xlsx
        print('txt文本数据结合成功')
    except Exception as e:
        data_xlsx = pd.merge(data_xlsx, dfs_o, how='left', on=['key_o'])
        data_xlsx = pd.merge(data_xlsx, dfs_d, how='left', on=['key_d'])
        list_dizhi = ['文件夹地址', 'key_o', 'key_d', 'originImage地址', 'detectImage地址']
        for i in range(len(list_dizhi)):
            data_xlsx[list_dizhi[i]] = data_xlsx[list_dizhi[i]].str.replace('W:', 'w:')
            data_xlsx[list_dizhi[i]] = data_xlsx[list_dizhi[i]].str.replace('w:', r'\\\\172.18.5.225\\data2')
            data_xlsx[list_dizhi[i]] = data_xlsx[list_dizhi[i]].str.replace('Z:', 'z:')
            data_xlsx[list_dizhi[i]] = data_xlsx[list_dizhi[i]].str.replace('z:', r'\\\\172.18.5.222\\data2')
        print('txt文本数据结合失败,可能不存在lable文本文件.\n仅进行表格信息与图像信息输出.\n', e)
        return data_xlsx


if __name__ == "__main__":
    # 记录时间
    start = time.time()

    # 数据源地址
    paths = r'W:\wangshuan\TAI40-II\TAI40-II_vs'
    path_inputs = [paths]
    # 数据清洗输出位置
    path_out = r'W:\wangshuan\TAI40-II\TAI40-II_vs\TAI40-II对比测试.xlsx'

    # xlsx  注意对表文件进行筛选 以 endswith=".xlsx"， 不以  endswith_false="data.xlsx"结束的文件
    data_xlsx = data_xlsx(endswith=".xlsx", endswith_false="data.xlsx", inwith_false="16")

    # 尝试与txt文本接触
    data_xlsx = data_txt(data_xlsx)

    # 提取问题数据
    data_error = func_error(data_xlsx)
    # 输出清洗数据
    append_df_to_excel(path_out, data_xlsx, sheet_name='data', startcol=0, startrow=0, index=False)

    # 输出可疑数据
    append_df_to_excel(path_out, data_error, sheet_name='error', startcol=0, startrow=0, index=False)
    # 记录时间
    end = time.time()
    print("代码运行耗时{:.2f}秒".format(end - start))
